package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Rent;
import ModelLayer.Sales;
import ModelLayer.SalesRental;


public class DBRent implements IFDBGeneric<Rent> {
	private Connection con;

	public DBRent()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	
	
	public ArrayList<Rent> getAll() {
		return  miscWhere("");
	}

	public ArrayList<Rent> getAll_byCustomerID(int cid){
		return miscWhere("cid=" +cid);
	}
	
	
	public Rent find(int invoiceNo) {
		return singleWhere("invoiceNo = '"+invoiceNo+"'");
	}
	
	public Rent find(String name) { return null; }
	
	public Rent insert(int invoiceNo) {
		return singleWhere("invoiceNo = "+invoiceNo);
	}
	
	

	public int delete(int invoiceNo)
	{
		int rc = -1;
		
		String query = "DELETE FROM Rent WHERE invoiceNo = "+invoiceNo;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't delete the Rental with invoiceNo "+invoiceNo);
		}
		
		
		return rc;
	}
	
	
	public int insert(Rent rent) throws Exception 
	{
		int rc = -1;
		
		String query1 = "INSERT INTO Rent (cid, invoiceNo, date, amount) VALUES "
				+ "('"+rent.getCid()+"','"+rent.getInvoiceNo()+"','"+rent.getDate()+"','"+rent.getAmount()+"')";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1, Statement.RETURN_GENERATED_KEYS);
			
			
			ResultSet rs = stmt.getGeneratedKeys();
	        if (rs.next())  rc = rs.getInt(1);
	        rs.close();
			
			if(rc > 0)
			{
				
				String query2 = "INSERT INTO Rent (period) VALUES "
					+ "("+rc+",'"+rent.getPeriod()+"')";
				
				rc = stmt.executeUpdate(query2);
			}
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't insert the Rental with InvoiceNo "+rent.getInvoiceNo());
		}
		
		
		return rc;
	}

	
	public int update(Rent rent) 
	{
		
			int rc = -1;
			
			String query1 = "UPDATE Rent SET date = '"+rent.getDate()+"',amount = "+rent.getAmount()+", Cid = "+rent.getCid()+" WHERE invoiceNo = "+rent.getInvoiceNo();
			
			
			try{
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(query1);
				
				if(rc > 0)
				{
					String query2 = "UPDATE Sales SET period = '"+rent.getPeriod()+"' WHERE invoiceNo = "+rent.getInvoiceNo();
					rc = stmt.executeUpdate(query2);
				}
				
				stmt.close();
			}
			catch(Exception ex){
				System.err.println("Couldn't update the Rental with invoice Number "+rent.getInvoiceNo());
			}
			
			
			return rc;
		}
	
	
	
	
	private Rent singleWhere(String wClause)
	{
		Rent rent =null;
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
				rent = buildRent(results);
			
			stmt.close();
		}
		catch(Exception ex){}
		
		return rent;
	}
	
	
	
	private ArrayList<Rent> miscWhere(String wClause)
	{
		ArrayList<Rent> rentals = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			
			while(results.next())
			{
				Rent rent = buildRent(results);
				rentals.add(rent);
			}
			
			stmt.close();
			
		}
		catch(Exception ex){}
		
		
		
		
		return rentals;
	}
	
	
	
	
	
	
	private Rent buildRent(ResultSet result)
	{
		Rent rent = new Rent();
		
		try{	
			rent.setInvoiceNo(result.getInt("invoiceNo"));
			rent.setDate(result.getDate("Date"));
			rent.setAmount(result.getDouble("Amount"));
			rent.setCid(result.getInt("Cid"));
			rent.setPeriod(result.getInt("Period"));
		}
		catch(Exception ex){}
		
		return rent;
	}
	
	
	private String buildQuery(String wClause)
	{
		String query = "Select InvoiceNo, Date, Amount, Cid, Period FROM SalesRental sr, Rent r WHERE sr.invoiceNo=r.invoiceNo";
		if(wClause.length() > 0) query = query + " AND " + wClause;
		
		return query;
	}

}
